package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.entity.Attendance;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * <p>
 * 考勤表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-07-10
 */
@Mapper
public interface AttendanceMapper extends BaseMapper<Attendance> {


    /**
     * 获取用户名字
     * @param id
     * @return
     */
    @Select("SELECT GROUP_CONCAT( b.`username` ) " +
            "FROM dragon_attendance a LEFT JOIN dragon_admin b ON FIND_IN_SET( b.id, a.admin_id ) " +
            "WHERE a.id = #{id} ")
    String setAdminName(Integer id);

    @Select("SELECT distinct times FROM (SELECT " +
            "    concat(date_format(#{date},'%Y-%m'),'-',LPAD(help_topic_id,2,'0')) times " +
            "            FROM " +
            "            mysql.help_topic " +
            "            WHERE " +
            "        date_format(concat(date_format(#{date},'%Y-%m'),'-',LPAD(help_topic_id,2,'0')),'%Y-%m-%d') < CONCAT(date_format(date_add(#{date},interval 1 month),'%Y-%m'),'-01') and help_topic_id !=0 " +
            "                    ORDER BY " +
            "                    help_topic_id desc) a")
    List<String> selectDateList(String date);

    /**
     * 考勤统计
     * @param adminIds
     * @param time
     * @param companyId
     * @return
     */
    @Select("<script>" +
            "with base as ( " +
            "select * from dragon_attendance where 1=1  and left(create_time,7) = #{time} " +
            "), " +
            "adminBase as( " +
            "select id,username from dragon_admin where 1=1  " +
            "<if test='adminIds.size != 0 and adminIds != \"\"'> "+
            "and id in " +
            "<foreach collection = 'adminIds' item = 'item' open = '(' close = ')' separator = ','> " +
            "#{item} " +
            "</foreach> " +
            "</if> " +
            "), " +
            "base1 as ( " +
            "select admin_id,in_clock_type,count(in_clock_type) numbers from ( " +
            "select da.id,da.admin_id,dac.in_clock_type,da.attendance_type from dragon_attendance_child dac left join base da on dac.attendance_id = da.id where dac.in_clock_type !=1) a " +
            "group by admin_id, in_clock_type " +
            "), " +
            "base2 as ( " +
            "select a.admin_id,ifnull(a.chidao,0) chidao,ifnull(b.zaotui,0) zaotui from " +
            "(select admin_id,numbers as chidao from base1 where in_clock_type = 2) a, " +
            "(select admin_id,numbers as zaotui from base1 where in_clock_type = 3) b where a.admin_id = b.admin_id " +
            "), " +
            "totalCount AS ( " +
            "SELECT M.*, ifnull( N.qingjia, 0 ) qingjia, ifnull( N1.queka, 0 ) queka  FROM (SELECT " +
            "a.id AS admin_id, " +
            "a.username AS admin_name, " +
            "ifnull( b.number, 0 ) number " +
            "FROM " +
            "adminBase a " +
            "LEFT JOIN ( SELECT admin_id, count( admin_id ) number FROM base WHERE attendance_type = 1 OR attendance_type = 3 GROUP BY admin_id ) b ON a.id = b.admin_id ) M  " +
            "LEFT JOIN ( SELECT admin_id, count( admin_id ) qingjia FROM base WHERE attendance_type = 2 GROUP BY admin_id ) N ON N.admin_id = M.admin_id " +
            "LEFT JOIN ( SELECT admin_id, count( admin_id ) queka FROM base WHERE attendance_type = 4 GROUP BY admin_id ) N1 ON N1.admin_id = M.admin_id   " +
            "), " +
            "totalCount1 as ( " +
            "select a.admin_id,a.admin_name,a.number shiji, ifnull(b.zaotui,0) zaotui, ifnull(b.chidao,0) chidao,  a.qingjia qingjia, a.queka queka  from totalCount a left join base2 b on a.admin_id = b.admin_id " +
            ") " +
            "select * from totalCount1  " +
            "</script>")
    @InterceptorIgnore(tenantLine = "true")
    List<Map<String,Object>> selectMap(List<Integer> adminIds, String time , String companyId);

    @Select("<script>" +
            "select M.* from (select a.*,aa.username, ad.`name` from dragon_attendance a  " +
            "LEFT JOIN dragon_admin aa on a.admin_id = aa.id " +
            "LEFT JOIN dragon_department ad on a.department_id =  ad.id )M WHERE 1=1  " +
            "<if test='adminId != null and adminId != \"\"'> " +
            "and M.admin_id = #{adminId} " +
            "</if> " +
            "<if test='time != null and time != \"\"'> " +
            "and LEFT(M.create_time,7) = #{time} " +
            "</if> " +
            "</script>")
    Page<Attendance> selectListBy(Page<Attendance> attendancePage, Integer adminId, String time);
}
